package com.hissy.common.office;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.InvocationTargetException;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class ExcelUtil<T> {

    //日志对象
    private static final Logger LOG = LoggerFactory.getLogger(ExcelUtil.class);

    private static final String EXCEL_SUFFIX_XLS = "xls";

    private static final String COMMA = ",";

    @SuppressWarnings("rawtypes")
    private static ExcelUtil ExcelUtil = new ExcelUtil();

    @SuppressWarnings("rawtypes")
    public static ExcelUtil getExcelUtilInstance() {
        return ExcelUtil;
    }

    public static Workbook exportExcelFile2007(String templateId, int beginRow) {
        // Excel工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();
        // Excel表单
        XSSFSheet sheet = workbook.createSheet();
        // Excel行：初始为表头
        XSSFRow row = sheet.createRow(beginRow);
        // Excel单元格
        XSSFCell cell = null;
        // Excel单元格样式
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        // 单元格样式设置：居中
        cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        // 单元格样式设置：文本自动换行
        cellStyle.setWrapText(true);
        // 表头数据：根据模板id获取相应的表头数据
        String[] excelHeader = getExcelHeaderByTemplateId(templateId, COMMA);

        /**
         * 写入Excel表头数据
         */
        setExcelHeader(row, cellStyle, excelHeader);
        return workbook;
    }

    private static void setExcelHeader(Row row, CellStyle cellStyle, String[] excelHeader) {
        Cell cell;
        if (excelHeader != null) {
            for (int i = 0; i < excelHeader.length; i++) {
                cell = row.createCell(i);
                cell.setCellValue(excelHeader[i]);
                cell.setCellStyle(cellStyle);
            }
        }
    }

    /**
     * 根据模板编号导出对应的Excel模板
     *
     * @param templateId 模板编号
     * @param beginRow   表头所在行，从0开始
     * @return 生成的Excel模板
     * @see [相关类/方法](可选)
     * @since [产品/模块版本](可选)
     */
    public static Workbook exportExcelFile2003(String templateId, int beginRow) {
        // Excel工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();
        // Excel表单
        HSSFSheet sheet = workbook.createSheet();
        // Excel行：初始为表头
        HSSFRow row = sheet.createRow(beginRow);
        // Excel单元格
        HSSFCell cell = null;
        // Excel单元格样式
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        // 单元格样式设置：居中
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 单元格样式设置：文本自动换行
        cellStyle.setWrapText(true);
        // 表头数据：根据模板id获取相应的表头数据
        String[] excelHeader = getExcelHeaderByTemplateId(templateId, COMMA);

        /**
         * 写入Excel表头数据
         */
        setExcelHeader(row, cellStyle, excelHeader);
        return workbook;
    }

    public static Workbook exportExcelFile2007(String templateId) {
        return exportExcelFile2007(templateId, 0);
    }

    /**
     * 根据模板编号导出对应的Excel模板，默认表头在0行
     *
     * @param templateId 模板编号
     * @return 生成的Excel模板
     * @see [相关类/方法](可选)
     * @since [产品/模块版本](可选)
     */
    public static Workbook exportExcelFile2003(String templateId) {
        return exportExcelFile2003(templateId, 0);
    }

    public static List<Map<Integer, String>> getImportExcelData(InputStream inputStream, String fileName,
                                                                int dataHeadRowIndex, int sheetIndex, int maxRow) throws
        IOException {
        return getImportExcelData(inputStream, fileName, dataHeadRowIndex, sheetIndex, maxRow, 2);
    }

    public static List<Map<Integer, String>> getImportExcelData8(InputStream inputStream, String fileName,
                                                                 int dataHeadRowIndex, int sheetIndex, int maxRow)
        throws IOException {
        return getImportExcelData(inputStream, fileName, dataHeadRowIndex, sheetIndex, maxRow, 8);
    }

    /**
     * 读取excel数据
     *
     * @param inputStream      导入的excel文件输入流
     * @param fileName         excel文件名
     * @param dataHeadRowIndex 指定第几行为数据头，从0开始
     * @param sheetIndex       指定sheet页，从0开始
     * @param maxRow
     * @param scale
     * @return 所有表单数据
     * @throws IOException Excel读取异常
     */
    private static List<Map<Integer, String>> getImportExcelData(InputStream inputStream, String fileName,
                                                                 int dataHeadRowIndex, int sheetIndex, int maxRow,
                                                                 int scale) throws IOException {
        // Excel工作薄
        Workbook workbook = null;
        // Excel表单
        Sheet sheet = null;
        // Excel行
        Row row = null;
        // 表单中的一行数据
        Map<Integer, String> dataMap = null;
        // 表单中的所有数据
        List<Map<Integer, String>> dataList = null;

        // 获取Excel工作簿对象
        if (EXCEL_SUFFIX_XLS.equalsIgnoreCase(fileName.substring(fileName.lastIndexOf(".") + 1, fileName.length()))) {
            workbook = new HSSFWorkbook(inputStream);
        } else {
            workbook = new XSSFWorkbook(inputStream);
        }

        // 获取表单对象
        sheet = workbook.getSheetAt(sheetIndex);
        // Excel行中是否是有效数据行
        boolean isValidRow = false;

        if (sheet.getPhysicalNumberOfRows() > maxRow) {
            //throw new CxpCommonException("MAX_EXCEL_IMPORT_ROW ", "最多只能导入" + maxRow + "条数据");
        }

        /**
         * 从表单第二行开始读取数据，读取整个表单中的数据
         */
        dataList = new ArrayList<Map<Integer, String>>();
        for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
            // 初始化是否有效数据行：非有效数据行
            isValidRow = false;

            row = sheet.getRow(i);
            if (row != null) {
                dataMap = new HashMap<Integer, String>();
                /**
                 * 根据模板表头长度,读取一行数据
                 */
                for (int j = 0; j < sheet.getRow(dataHeadRowIndex).getPhysicalNumberOfCells(); j++) {
                    if (row.getCell(j) == null) {
                        dataMap.put(j, "");
                    } else {
                        FormulaEvaluator e = workbook.getCreationHelper().createFormulaEvaluator();
                        String content = null;
                        if (scale == 8) {
                            content = parseExcel(e.evaluateInCell(row.getCell(j)), 8);
                        } else {
                            content = parseExcel(e.evaluateInCell(row.getCell(j)));
                        }
                        if (!StringUtils.isBlank(content)) {
                            isValidRow = true;
                        }
                        dataMap.put(j, content);
                    }
                }

                /**
                 * 读取完一条记录，如果是有效数据行，则加入返回结果中
                 */
                if (isValidRow) {
                    dataList.add(dataMap);
                }
            }
        }
        return dataList;
    }

    /**
     * 解析Excel内容
     *
     * @param cell
     * @return
     */
    private static String parseExcel(Cell cell) {
        String result = new String();
        switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_NUMERIC:// 数字类型
                if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
                    SimpleDateFormat sdf = null;
                    if (cell.getCellStyle().getDataFormat() == HSSFDataFormat
                        .getBuiltinFormat("h:mm")) {
                        sdf = new SimpleDateFormat("HH:mm");
                    } else {// 日期
                        sdf = new SimpleDateFormat("yyyy-MM-dd");
                    }
                    Date date = cell.getDateCellValue();
                    result = sdf.format(date);
                } else if (cell.getCellStyle().getDataFormat() == 58) {
                    // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    double value = cell.getNumericCellValue();
                    Date date = org.apache.poi.ss.usermodel.DateUtil
                        .getJavaDate(value);
                    result = sdf.format(date);
                } else {
                    BigDecimal value = new BigDecimal(cell.getNumericCellValue());
                    CellStyle style = cell.getCellStyle();
                    DecimalFormat format = new DecimalFormat();
                    String temp = style.getDataFormatString();
                    // 单元格设置成常规
                    if ("General".equals(temp)) {
                        format.applyPattern("#");
                    }
                    BigDecimal f1 = value.setScale(2, BigDecimal.ROUND_HALF_UP);
                    result = f1 + "";
                    String nums[] = result.split("\\.");
                    if (nums.length == 2) {
                        if ("00".equals(nums[1])) {
                            result = nums[0];
                        }
                    }
                }
                break;
            case HSSFCell.CELL_TYPE_STRING:
                result = cell.getRichStringCellValue().toString();
                break;
            case HSSFCell.CELL_TYPE_BLANK:
                result = "";
            default:
                result = "";
                break;
        }
        return result;
    }

    private static String parseExcel(Cell cell, int scale) {
        String result;
        switch (cell.getCellType()) {
            // 数字类型
            case HSSFCell.CELL_TYPE_NUMERIC:
                // 处理日期格式、时间格式
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = null;
                    if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
                        sdf = new SimpleDateFormat("HH:mm");
                    } else {// 日期
                        sdf = new SimpleDateFormat("yyyy-MM-dd");
                    }
                    Date date = cell.getDateCellValue();
                    result = sdf.format(date);
                } else if (cell.getCellStyle().getDataFormat() == 58) {
                    // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    double value = cell.getNumericCellValue();
                    Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
                    result = sdf.format(date);
                } else {
                    BigDecimal value = new BigDecimal(cell.getNumericCellValue());
                    CellStyle style = cell.getCellStyle();
                    DecimalFormat format = new DecimalFormat();
                    String temp = style.getDataFormatString();
                    // 单元格设置成常规
                    if ("General".equals(temp)) {
                        format.applyPattern("#");
                    }
                    BigDecimal f1 = value.setScale(scale, BigDecimal.ROUND_HALF_UP);
                    result = f1 + "";
                    String nums[] = result.split("\\.");
                    if (nums.length == 2) {
                        if (StringUtils.countMatches(nums[1], "0") == scale) {
                            result = nums[0];
                        } else {
                            char[] chars = nums[1].toCharArray();
                            for (int i = chars.length; i > 0; i--) {
                                if (chars[i - 1] == '0') {
                                    result = result.substring(0, result.length() - 1);
                                } else {
                                    break;
                                }
                            }
                        }
                    }
                }
                break;
            case HSSFCell.CELL_TYPE_STRING:
                result = cell.getRichStringCellValue().toString();
                break;
            case HSSFCell.CELL_TYPE_BLANK:
                result = "";
            default:
                result = "";
                break;
        }
        return result;
    }

    /**
     * 功能描述: <br>
     * 根据模板编码获取对应Excel表头数据
     *
     * @return Excel表头数据
     * @see [相关类/方法](可选)
     * @since [产品/模块版本](可选)
     */
    public static String[] getExcelHeaderByTemplateId(String templateHeader, String splitStr) {
        /**
         * 获取线索导出模板表头数据
         */
        if (templateHeader != null) {
            return templateHeader.split(splitStr);
        }

        return null;
    }

    public List<T> readExcel(InputStream is, String fileName, int allowCellNumber, Class<T> c, String[] methods)
        throws IOException {
        String extension = fileName.lastIndexOf(".") == -1 ? "" : fileName.substring(fileName.lastIndexOf(".") + 1);
        if ("xls".equals(extension)) {
            return read2003Excel(is, allowCellNumber, c, methods);
        } else if ("xlsx".equals(extension)) {
            return read2007Excel(is, allowCellNumber, c, methods);
        } else {
            throw new IOException("不支持的文件类型");
        }
    }

    /**
     * 读取Office 2007 excel
     *
     * @throws IOException
     */
    private List<T> read2007Excel(InputStream is, int allowCellNumber, Class<T> c, String[] methods)
        throws IOException {

        List<T> list = new LinkedList<T>();
        // 构造 XSSFWorkbook 对象，strPath 传入文件路径
        XSSFWorkbook xwb = new XSSFWorkbook(is);
        // 读取第一章表格内容
        XSSFSheet sheet = xwb.getSheetAt(0);

        if (sheet.getRow(0).getLastCellNum() != allowCellNumber) {
            //throw new CxpCommonException("common.error.message", "非法的excel文件格式,请使用正确的导出模板");
        }

        Object value = new Object();
        int counter = 0;
        int totalRowNumber = sheet.getLastRowNum() > sheet.getPhysicalNumberOfRows() ? sheet.getLastRowNum()
            : sheet.getPhysicalNumberOfRows();
        getContent(allowCellNumber, c, methods, list, sheet, value, counter, totalRowNumber);
        return list;
    }

    private void getContent(int allowCellNumber, Class<T> c, String[] methods, List<T> list, Sheet sheet,
                            Object value, int counter, int totalRowNumber) {
        Row row;
        Cell cell;
        try {
            for (int i = sheet.getFirstRowNum(); i < totalRowNumber; i++) {

                row = sheet.getRow(i);

                if (i == 0) {
                    counter++;
                    continue;
                } else {
                    counter++;
                    if (row == null) {
                        T linked = c.newInstance();
                        for (int j = 0; j < allowCellNumber; j++) {
                            c.getMethod(methods[j], String.class).invoke(linked, "");
                        }
                        c.getMethod(methods[methods.length - 1], String.class).invoke(linked, (counter) + "");
                        list.add(linked);
                        continue;
                    }
                }
                T linked = c.newInstance();
                int cellNumber = (allowCellNumber < 1) ? row.getLastCellNum() : (allowCellNumber - 1);
                for (int j = row.getFirstCellNum(); j <= cellNumber; j++) {
                    cell = row.getCell(j);
                    if (cell == null) {
                        continue;
                    }
                    // 格式化 number String
                    DecimalFormat df = new DecimalFormat("#.########");
                    switch (cell.getCellType()) {
                        case XSSFCell.CELL_TYPE_FORMULA:
                            LOG.info(i + "行" + j + " 列 is Formula type");
                            switch (cell.getCachedFormulaResultType()) {
                                case Cell.CELL_TYPE_NUMERIC:
                                    value = df.format(cell.getNumericCellValue());
                                    break;
                                case Cell.CELL_TYPE_STRING:
                                    value = cell.getRichStringCellValue();
                                    break;
                                default:
                                    break;
                            }
                            break;
                        case XSSFCell.CELL_TYPE_STRING:
                            LOG.info(i + "行" + j + " 列 is String type");
                            value = cell.getStringCellValue();
                            break;
                        case XSSFCell.CELL_TYPE_NUMERIC:
                            LOG.info(i + "行" + j
                                + " 列 is Number type ; DateFormt:"
                                + cell.getCellStyle().getDataFormatString());

                            value = df.format(cell.getNumericCellValue());
                            break;
                        case XSSFCell.CELL_TYPE_BOOLEAN:
                            LOG.info(i + "行" + j + " 列 is Boolean type");
                            value = cell.getBooleanCellValue();
                            break;
                        case XSSFCell.CELL_TYPE_BLANK:
                            LOG.info(i + "行" + j + " 列 is Blank type");
                            value = "";
                            break;
                        default:
                            LOG.info(i + "行" + j + " 列 is default type");
                            value = cell.toString();
                    }
                    if (value == null || "".equals(value)) {
                        continue;
                    }

                    c.getMethod(methods[j], String.class).invoke(linked, value);
                }
                c.getMethod(methods[methods.length - 1], String.class).invoke(linked, (counter) + "");
                list.add(linked);
            }

        } catch (IllegalAccessException e) {
            LOG.error("IllegalAccessException" + e.getMessage(), e);
        } catch (IllegalArgumentException e) {
            LOG.error("IllegalArgumentException" + e.getMessage(), e);
        } catch (InvocationTargetException e) {
            LOG.error("InvocationTargetException" + e.getMessage(), e);
        } catch (NoSuchMethodException e) {
            LOG.error("NoSuchMethodException" + e.getMessage(), e);
        } catch (SecurityException e) {
            LOG.error("SecurityException" + e.getMessage(), e);
        } catch (InstantiationException e) {
            LOG.error("InstantiationException" + e.getMessage(), e);
        }
    }

    /**
     * 读取 office 2003 excel
     *
     * @throws IOException
     */
    private List<T> read2003Excel(InputStream is, int allowCellNumber, Class<T> c, String[] methods)
        throws IOException {
        List<T> list = new LinkedList<T>();
        HSSFWorkbook hwb = new HSSFWorkbook(is);
        HSSFSheet sheet = hwb.getSheetAt(0);
        Object value = new Object();
        int counter = 0;
        int totalRowNumber = sheet.getLastRowNum() > sheet.getPhysicalNumberOfRows() ? sheet.getLastRowNum()
            : sheet.getPhysicalNumberOfRows();
        getContent(allowCellNumber, c, methods, list, sheet, value, counter, totalRowNumber);
        return list;
    }
}
